import pymysql
from pymysql import Error

class DatabaseManager:
    def __init__(self, host, user, password, database, port=3306):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = port
        self.connection = None
        self.cursor = None

    def connect(self):
        try:
            self.connection = pymysql.connect(
                host=self.host,
                user=self.user,
                password=self.password,
                database=self.database,
                port=self.port,
                connect_timeout=10
            )

            if self.connection.open:
                self.cursor = self.connection.cursor()
                return True
            return False
        except Error as e:
            return False
        except Exception as e:
            return False

    def create_database_if_not_exists(self):
        try:
            # 临时连接到MySQL服务器但不指定数据库
            temp_conn = pymysql.connect(
                host=self.host,
                user=self.user,
                password=self.password,
                port=self.port,
                connect_timeout=10
            )
            temp_cursor = temp_conn.cursor()

            # 创建数据库（如果不存在）
            temp_cursor.execute(f"CREATE DATABASE IF NOT EXISTS {self.database}")

            # 选择数据库
            temp_cursor.execute(f"USE {self.database}")

            temp_cursor.close()
            temp_conn.close()
            return True
        except Exception as e:
            return False

    def create_tables(self):
        try:
            # 确保数据库存在
            if not self.create_database_if_not_exists():
                return False

            self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(50) NOT NULL UNIQUE,
                password VARCHAR(255) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
            """)

            self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS calories (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT,
                date DATE NOT NULL,
                breakfast VARCHAR(100),
                breakfast_calories INT,
                lunch VARCHAR(100),
                lunch_calories INT,
                dinner VARCHAR(100),
                dinner_calories INT,
                total_calories INT,
                FOREIGN KEY (user_id) REFERENCES users(id)
            )
            """)

            self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS weights (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT,
                date DATE NOT NULL,
                weight DECIMAL(5,1) NOT NULL,
                FOREIGN KEY (user_id) REFERENCES users(id)
            )
            """)
            self.connection.commit()
            return True
        except Error as e:
            return False

    def create_user(self, username, password):
        try:
            self.cursor.execute("INSERT INTO users (username, password) VALUES (%s, %s)",
                                (username, password))
            self.connection.commit()
            return self.cursor.lastrowid
        except Error as e:
            return None

    def authenticate_user(self, username, password):
        try:
            self.cursor.execute("SELECT id, password FROM users WHERE username = %s", (username,))
            result = self.cursor.fetchone()
            if result and password == result[1]:
                return result[0]  # Return user_id
            return None
        except Error as e:
            return None

    def add_calories_record(self, user_id, date, breakfast, b_cal, lunch, l_cal, dinner, d_cal, total):
        try:
            query = """
            INSERT INTO calories 
            (user_id, date, breakfast, breakfast_calories, lunch, lunch_calories, dinner, dinner_calories, total_calories)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            values = (user_id, date, breakfast, b_cal, lunch, l_cal, dinner, d_cal, total)
            self.cursor.execute(query, values)
            self.connection.commit()
            return True
        except Error as e:
            return False

    def add_weight_record(self, user_id, date, weight):
        try:
            self.cursor.execute("INSERT INTO weights (user_id, date, weight) VALUES (%s, %s, %s)",
                                (user_id, date, weight))
            self.connection.commit()
            return True
        except Error as e:
            return False

    def get_calories_history(self, user_id):
        try:
            self.cursor.execute("""
            SELECT id, date, breakfast, breakfast_calories, lunch, lunch_calories, dinner, dinner_calories, total_calories
            FROM calories 
            WHERE user_id = %s 
            ORDER BY date DESC
            """, (user_id,))
            return self.cursor.fetchall()
        except Error as e:
            return []

    def get_weight_history(self, user_id):
        try:
            self.cursor.execute("SELECT id, date, weight FROM weights WHERE user_id = %s ORDER BY date DESC", (user_id,))
            return self.cursor.fetchall()
        except Error as e:
            return []

    def close(self):
        if self.connection:
            self.cursor.close()
            self.connection.close()